package com.shop.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shop.bean.Products;
import com.shop.util.DBConnection;
//创建数据访问层实现类，定义插入、删除、更新和查询等操作数据库的方法
public class ProductsDAOImpl implements ProductsDAO {
	//定义插入方法
	/* (non-Javadoc)
	 * @see com.shop.dao.ProductsDAO#addProduct(com.shop.bean.Products)
	 */
	@Override
	public void addProduct(Products product){
		Connection conn = DBConnection.getConnection();	//static方法可用类名直接调用
		String addSQL = "insert into products(name,price,agio)values(?,?,?)";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(addSQL);
			pstmt.setString(1,product.getName());
			pstmt.setDouble(2,product.getPrice());
			pstmt.setDouble(3,product.getAgio());
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
	}
	//定义删除方法
	/* (non-Javadoc)
	 * @see com.shop.dao.ProductsDAO#deleteProduct(int)
	 */
	@Override
	public void deleteProduct(int productid){
		Connection conn = DBConnection.getConnection();	//static方法可用类名直接调用
		String addSQL = "delete from products where id = "+productid;
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(addSQL);
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
	}
	//定义查询All方法
	/* (non-Javadoc)
	 * @see com.shop.dao.ProductsDAO#findAllProducts()
	 */
	@Override
	public List<Products> findAllProducts(){
		Connection conn = DBConnection.getConnection();
		String addSQL = "select id,name,price,agio from products";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Products> ls = new ArrayList<Products>();
		try{
			pstmt = conn.prepareStatement(addSQL);
			rs = pstmt.executeQuery(addSQL);
			while(rs.next()){
				Products p = new Products();
				p.setId(rs.getInt("id"));
				p.setName(rs.getString("name"));
				p.setPrice(rs.getDouble("price"));
				p.setAgio(rs.getDouble("agio"));
				ls.add(p);
			}
		}catch(SQLException e){
			e.printStackTrace();
		}
		DBConnection.close(pstmt);
		DBConnection.close(conn);
		return ls;
	}
	//定义更新方法
	/* (non-Javadoc)
	 * @see com.shop.dao.ProductsDAO#updateProduct(com.shop.bean.Products)
	 */
	@Override
	public void updateProduct(Products product){
		Connection conn = DBConnection.getConnection();
		String addSQL = "update products set name=?,price=?,agio=? where id=?";
		PreparedStatement pstmt = null;
		try{
			pstmt = conn.prepareStatement(addSQL);
			pstmt.setString(1,product.getName());
			pstmt.setDouble(2,product.getPrice());
			pstmt.setDouble(3,product.getAgio());
			pstmt.setDouble(4,product.getId());
			pstmt.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
	}
	//定义查询方法by id
	/* (non-Javadoc)
	 * @see com.shop.dao.ProductsDAO#findProductById(int)
	 */
	@Override
	public Products findProductById(int productid){
		Connection conn = DBConnection.getConnection();
		String addSQL = "select name,price,agio from products where id="+productid;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Products p = new Products();
		try{
			pstmt = conn.prepareStatement(addSQL);
			rs = pstmt.executeQuery();
			p.setName(rs.getString("name"));
			p.setPrice(rs.getDouble("price"));
			p.setAgio(rs.getDouble("agio"));
			
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			DBConnection.close(pstmt);
			DBConnection.close(conn);
		}
		return p;
	}
}
